﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Phoenix.AppCode.Entities;
using System.Data.SqlClient;
using System.Data;

namespace Phoenix.AppCode.Data
{
    public class PortShippingScheduleDAO : BaseDAO
    {
        public DataTable PortShippingGetDate(int id, String dfrom, String dto)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_PortShippingScheduleListByIdPort_Date", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Port", SqlDbType.Int)).Value = id;
                command.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.NVarChar)).Value = dfrom;
                command.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.NVarChar)).Value = dto;
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                command.ExecuteNonQuery();
                return dt;
            }
        }
        public DataTable PortShippingGetListID(int id)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_PortShippingGetID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = id;
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                command.ExecuteNonQuery();
                return dt;
            }
        }

        #region Backend method
        /// <summary>
        /// Insert PortShippingSchedule
        /// </summary>
        /// <param name="portshippingschedule"></param>
        public void Create(PortShippingSchedule portshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleCreate", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Port", portshippingschedule.ID_Port));
                command.Parameters.Add(new SqlParameter("@FEEDER", portshippingschedule.FEEDER));
                command.Parameters.Add(new SqlParameter("@VOY", portshippingschedule.VOY));
                command.Parameters.Add(new SqlParameter("@ETDHCM", portshippingschedule.ETDHCM));
                command.Parameters.Add(new SqlParameter("@CONNECTINGVESSEL", portshippingschedule.CONNECTINGVESSEL));
                command.Parameters.Add(new SqlParameter("@ETD", portshippingschedule.ETD));
                command.Parameters.Add(new SqlParameter("@ETADEST", portshippingschedule.ETADEST));
                command.Parameters.Add(new SqlParameter("@PostUser", portshippingschedule.PostUser));

                connection.Open();
                command.ExecuteNonQuery();
                //if (paramId.Value != DBNull.Value)
                //PortShippingSchedule.ID = (long)paramId.Value;
                //else
                //  throw new DataAccessException(String.Format(Resources.Strings.CreateEntityException, ""));
            }
        }

        /// <summary>
        /// Edit PortShippingSchedule 
        /// </summary>
        /// <param name=""></param>
        public void Edit(PortShippingSchedule portshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleEdit", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_PortShippingSchedule", portshippingschedule.ID_PortShippingSchedule));
                command.Parameters.Add(new SqlParameter("@ID_Port", portshippingschedule.ID_Port));
                command.Parameters.Add(new SqlParameter("@FEEDER", portshippingschedule.FEEDER));
                command.Parameters.Add(new SqlParameter("@VOY", portshippingschedule.VOY));
                command.Parameters.Add(new SqlParameter("@ETDHCM", portshippingschedule.ETDHCM));
                command.Parameters.Add(new SqlParameter("@CONNECTINGVESSEL", portshippingschedule.CONNECTINGVESSEL));
                command.Parameters.Add(new SqlParameter("@ETD", portshippingschedule.ETD));
                command.Parameters.Add(new SqlParameter("@ETADEST", portshippingschedule.ETADEST));
                command.Parameters.Add(new SqlParameter("@EditUser", portshippingschedule.EditUser));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "", .ID));
            }
        }

        /// <summary>
        /// On/Off view
        /// </summary>
        /// <param name="portshippingschedule"></param>
        public void OnOff(PortShippingSchedule portshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleOnOff", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_PortShippingSchedule", portshippingschedule.ID_PortShippingSchedule));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "", .ID));
            }
        }


        /// <summary>
        /// Delete PortShippingSchedule 
        /// </summary>
        /// <param name=""></param>
        public void Delete(PortShippingSchedule portshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleDelete", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_PortShippingSchedule", portshippingschedule.ID_PortShippingSchedule));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.DeleteEntityException, "", .ID));
            }
        }

        /// <summary>
        /// Show PortShippingSchedule  All
        /// </summary>
        /// <returns></returns>
        public DataTable PortShippingScheduleList()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleList", connection);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show PortShippingSchedule All
        /// </summary>
        /// <returns></returns>
        public DataTable PortShippingScheduleListById(int idport)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleListById", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@ID_Port", SqlDbType.Int).Value = idport;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show data by ID
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public DataTable PortShippingScheduleList_GetByID(int idportshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ID_PortShippingSchedule", SqlDbType.Int).Value = idportshippingschedule;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Get ID to Edit or Delete
        /// </summary>
        /// <param name="idportshippingschedule"></param>
        /// <returns></returns>
        public PortShippingSchedule GetPortShippingScheduleByID(int idportshippingschedule)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spPortShippingScheduleEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_PortShippingSchedule", idportshippingschedule));

                connection.Open();
                PortShippingSchedule PortShippingSchedule = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                        PortShippingSchedule = CreatePortShippingScheduleFromReader(reader);
                    //else
                    //   throw new DataAccessException(String.Format(Resources.Strings.GetEntityException, "", ID));
                }

                return PortShippingSchedule;
            }
        }

        private PortShippingSchedule CreatePortShippingScheduleFromReader(IDataReader reader)
        {
            PortShippingSchedule idportshippingschedule = new PortShippingSchedule();
            idportshippingschedule.ID_PortShippingSchedule = (int)reader["ID_PortShippingSchedule"];
            idportshippingschedule.ID_Port = (int)reader["ID_Port"];
            idportshippingschedule.FEEDER = (string)reader["FEEDER"];
            idportshippingschedule.VOY = (string)reader["VOY"];
            idportshippingschedule.ETDHCM = (DateTime)reader["ETDHCM"];
            idportshippingschedule.CONNECTINGVESSEL = (string)reader["CONNECTINGVESSEL"];
            idportshippingschedule.ETD = (string)reader["ETD"];
            idportshippingschedule.ETADEST = (DateTime)reader["ETADEST"];
            idportshippingschedule.PostDate = (DateTime)reader["PostDate"];
            idportshippingschedule.PostUser = (string)reader["PostUser"];
            idportshippingschedule.EditDate = (DateTime)reader["EditDate"];
            idportshippingschedule.EditUser = (string)reader["EditUser"];
            idportshippingschedule.Visible = (Boolean)reader["Visible"];
            return idportshippingschedule;
        }
        #endregion
    }
}